package excel.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONPObject;
import com.alibaba.fastjson.parser.Feature;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import excel.bean.Emp;
import excel.csv_excel.CSVUtils;
import excel.service.IEmpService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/emp")
public class EmpController {
    @Resource
    private IEmpService is;

    @RequestMapping("/list")
    public String list(Model model){
        List<Emp> emplist = is.selemps();
     System.out.println("list size "+emplist.size());
       model.addAttribute("emplist",emplist);
       return "list";
    }
    @RequestMapping("/input")
    public String inputexcel(MultipartFile ff, HttpServletResponse response,Model model) throws IOException {
        //创建临时file对象
        List<Emp> empList=new ArrayList<>();
        File file= null;
        System.out.println("文件名"+ff);
        try {
            file = File.createTempFile("tmp", null);
            //把MultipartFile对象转换成java.io.FileUI对象
            ff.transferTo(file);
            InputStream inputStream = new FileInputStream(file);
            HSSFWorkbook  workbook = new HSSFWorkbook(inputStream);
            //获取工作表
            HSSFSheet sheet = workbook.getSheetAt(0);
            //循环行()
            for (int i=1;i<=sheet.getLastRowNum();i++){
                HSSFRow row = sheet.getRow(i);
                Emp emp = new Emp();
                //姓名，中文

                    emp.setEname(row.getCell(0).getStringCellValue());
                    System.out.println(row.getCell(0).getStringCellValue());
                    //密码，存数字的单元格不能用getStringCellValue()获取因为单元格格式是numeric
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    System.out.println("第一单元格"+row.getCell(1).getStringCellValue());
                    emp.setPwd(row.getCell(1).getStringCellValue());
                    emp.setSex(row.getCell(2).getStringCellValue());

                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    emp.setAge(Integer.parseInt(row.getCell(3).getStringCellValue()));

                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    emp.setIdcard(row.getCell(4).getStringCellValue());

                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    emp.setTel(row.getCell(5).getStringCellValue());
                    emp.setBirth(row.getCell(6).getStringCellValue());
                    emp.setFace(row.getCell(7).getStringCellValue());

                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    emp.setDid(Integer.parseInt(row.getCell(8).getStringCellValue()));
                    //一行数据做一次添加
                 is.saveemp(emp);

                }


        } catch (IOException e) {
            e.printStackTrace();
        }
        return "redirect:list";//重定向，重新调用查找的方法

    }
    @RequestMapping("/exportemp") //导出数据，下载到浏览器
    public  void exportExcel(HttpServletResponse response){
        //先查出学生的全部的数据
        List<Emp> list = is.selemps();
        HSSFWorkbook hssfWorkbook=new HSSFWorkbook();//先创建工作书
        HSSFSheet hssfSheet=hssfWorkbook.createSheet("sheet");//创建一个excel
        HSSFRow hssfRow=hssfSheet.createRow(0);//创建第一行
        HSSFCell hssfCell0=  hssfRow.createCell(0);//第一个单元格
        hssfCell0.setCellValue("员工信息");
        HSSFRow row1=hssfSheet.createRow(1);//创建第二行
        HSSFCell hssfCell1=  row1.createCell(0);//第一个单元格
        hssfCell1.setCellValue("编号");
        HSSFCell cell11 = row1.createCell(1);
        cell11.setCellValue("姓名");

        HSSFCell cell12 = row1.createCell(2);
        cell12.setCellValue("性别");

        HSSFCell cell13 = row1.createCell(3);
        cell13.setCellValue("年龄");

        HSSFCell cell14 = row1.createCell(4);
        cell14.setCellValue("身份证");

        HSSFCell cell15 = row1.createCell(5);
        cell15.setCellValue("电话");

        HSSFCell cell16 = row1.createCell(6);
        cell16.setCellValue("出生");

        HSSFCell cell17 = row1.createCell(7);
        cell17.setCellValue("部门编号");
        //从第三行开始，将数据写入，前两行是中文，编号这一行，信息这一行
        for(int i=2;i<list.size()+2;i++){//这里加2 是为了后面的行数的下标
            Emp emp=list.get(i-2);//这就是从0，1，2.3开始了，因为i的初始值是2
            HSSFRow row=hssfSheet.createRow(i);//从第三行开始
            HSSFCell hssfCell=row.createCell(0);//创建第三行第一个单元格
            System.out.println("编号"+emp.getId());
            hssfCell.setCellValue(emp.getId());//将编号的值放入
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(emp.getEname());

            HSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(emp.getSex());

            HSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(emp.getAge());

            HSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(emp.getIdcard());

            HSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(emp.getTel());

            HSSFCell cell6 = row.createCell(6);
            if(emp.getBirth()==null){
                cell6.setCellValue("");//生日为空
            }else{
                SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
                String birth = sf.format(emp.getBirth());//转化为格式日期
                cell6.setCellValue(birth);
            }

            HSSFCell cell7 = row.createCell(7);
            cell7.setCellValue(emp.getDid());

        }
        //将第一行合并单元格
        //合并单元格
        hssfSheet.addMergedRegion(new CellRangeAddress(0,0,0,7));
        //标题样式
        HSSFCellStyle styletitle = hssfWorkbook.createCellStyle();
        styletitle.setAlignment(HorizontalAlignment.CENTER);
        styletitle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont fonttitle = hssfWorkbook.createFont();
        fonttitle.setFontHeightInPoints((short)20);
        fonttitle.setBold(true);
        styletitle.setFont(fonttitle);
        cell11.setCellStyle(styletitle);

        //行高
        hssfRow.setHeightInPoints((float) 30);
        row1.setHeightInPoints((float) 20);
        //表头
        HSSFCellStyle style2 = hssfWorkbook.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont font2 = hssfWorkbook.createFont();
        font2.setBold(true);
        style2.setFont(font2);
        hssfCell0.setCellStyle(style2);
        cell11.setCellStyle(style2);
        cell12.setCellStyle(style2);
        cell13.setCellStyle(style2);
        cell14.setCellStyle(style2);
        cell15.setCellStyle(style2);
        cell16.setCellStyle(style2);
        cell17.setCellStyle(style2);

        //调整部分列宽
        hssfSheet.setColumnWidth(4,(int)35.7*250);
        hssfSheet.setColumnWidth(5,(int)35.7*220);
        hssfSheet.setColumnWidth(6,(int)35.7*200);
          //将excel写入输出流，并且，通过浏览器下载
        ByteArrayOutputStream byteArrayOutputStream=null;
        byte b[]=null;
        byteArrayOutputStream=new ByteArrayOutputStream();
        //将excel写入到流中
        try {
            hssfWorkbook.write(byteArrayOutputStream);
            b=byteArrayOutputStream.toByteArray();//得到位数组
            byteArrayOutputStream.flush();
            byteArrayOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        OutputStream outputStream=null;
        try {

            response.setContentType("application/force-download;charset=utf-8");//设置字符编码

            //设置响应头，控制浏览器下载该文件
            response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("student.xls", "UTF-8"));
            outputStream=response.getOutputStream();//从浏览器响应获取输出流
            outputStream.write(b);//将excel的流放入
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
@RequestMapping("/inputcsv")
   public String uploadcsv(MultipartFile csvff,Model model){
    try {
        //上传内容不能为空
        if (csvff.isEmpty()) {
            return "500";
        }
        File file = CSVUtils.uploadFile(csvff);
        List<List<String>> userRoleLists = CSVUtils.readCSV(file.getPath(), 10);//这个是显示的列数
        System.out.println("读取的数据"+userRoleLists);
        List<Emp> empList=new ArrayList<>();
        System.out.println("大小"+userRoleLists.size());
        //要显示出去的值
        List<String > stringList=new ArrayList<>();
        for(List<String> list : userRoleLists) {
            stringList = new ArrayList<>();
            stringList = list;
            System.out.println(stringList);


            System.out.println("stringList" + stringList);
            for (int i = 0; i < 1; i++) {//循环一次就可以了
                System.out.println("大小" + stringList.size());
                Emp emp = new Emp();
                emp.setId(Integer.parseInt(stringList.get(0)));
                String heads = "编号,用户名,密码,性别,年龄,身份证,电话,生日,图片,idd";
                emp.setEname(stringList.get(1));//与上面对应
                emp.setPwd(stringList.get(2));
                emp.setSex(stringList.get(3));
                emp.setAge(Integer.parseInt(stringList.get(4)));

                emp.setIdcard(stringList.get(5));
                emp.setTel(stringList.get(6));
                SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy/MM/dd");//不能有空格，要和数据库的数据类型对应

                String dd=stringList.get(7);
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");



                emp.setBirth(dd);


                /**
                 * 可以使用java中的日期格式化工具java.text.SimpleDateFormat来格式化,以下为示例代bai码:
                 * 1.创建java.text.SimpleDateFormat实例
                 * java.text.SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
                 * 参数说明,"yyyy-MM-dd"表示日期的格式,也可以是这样的 yyyy-MM-dd HH:mm:ss注意大小写
                 * 2.将Date对象转成字符串
                 * Date date = new Date();
                 * String str = sdf.format(date);
                 * System.out.println(str);
                 * 3.将字符串转成Date
                 * String str = "2013-12-12";
                 * Date date = sdf.parse(str);
                 * System.out.prinltn(date);
                 * 注意,SimpleDateFormat解析工具,指定的format以后,只能解析指定格式的日期，例如指定了yyyy-MM-dd则不可以再去解析 yyyy-MM-dd HH:mm:ss格式。
                 */

                emp.setFace(stringList.get(8));
                emp.setDid(Integer.parseInt(stringList.get(9)));

                System.out.println("要显示的" + emp);
             //把要新加的加入数据库
                //is.saveemp(emp);


            }
        }

        file.delete();

    } catch (Exception e) {
        e.printStackTrace();
    }

    return "redirect:list";//重定向查数据库
}




    private static Logger logger = LoggerFactory.getLogger(Controller.class);





    /**
     * @param response
     * @return
     * @Description 下载CSV
     **/
    @GetMapping("/exportemptocsv")
    public String downloadAllUserRoleCSV(HttpServletResponse response) throws IOException {


        List<Emp> list = is.selemps();
        System.out.println("emplist"+list);
        List<String []> valuelist=new ArrayList<>();

        for(Emp emp: list) {
            System.out.println(emp);
//            //LinkedHashMap<String, Object> json = JSON.parseObject(String.valueOf(emp),LinkedHashMap.class, Feature.OrderedField);
//            ObjectMapper objectMapper=new ObjectMapper();
//     String   empstr=    objectMapper.writeValueAsString(emp).toString();
            String   empstr=emp.getId()+","+
                    emp.getEname()+ ","+
                    emp.getPwd()+ ","+
                    emp.getSex()+ ","+
                    emp.getAge()+ ","+
                    emp.getIdcard()+ ","+
                    emp.getTel()+ ""+    //加上引号，这样不会用科学计数法
                    emp.getBirth()+ ","+
                    emp.getFace()+ ","+
                    emp.getDid();
            System.out.println("empstr"+empstr);
            String value []=empstr.split(",");
            valuelist.add(value);
        }

        System.out.println("liststr"+valuelist);

        System.out.println("导出cvs"+list);
        String heads="编号,用户名,密码,性别,年龄,身份证,电话,生日,图片,idd";
        int column=heads.split(",").length;
        String head[]=heads.split(",");
        System.out.println("表头是"+head);
        System.out.println("列数为"+column);
        String filename="emp";


       //先创建
         File file = CSVUtils.makeTempCSV(filename, head, valuelist);
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition", "attachment;fileName=" + filename +".csv");
        //下载
        CSVUtils.downloadFile(response, file);
        return null;
    }

    /**
     * @return
     * @Description 上传CSV
     * @Param file
     **/
    @PostMapping(value = "/upload")
    public String upload(@RequestParam("file") MultipartFile multipartFile) {
        try {
            //上传内容不能为空
            if (multipartFile.isEmpty()) {
                return "500";
            }
            File file = CSVUtils.uploadFile(multipartFile);
            List<List<String>> userRoleLists = CSVUtils.readCSV(file.getPath(), 2);
            //service.doSth(userRoleLists);
            file.delete();
            return "200";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "500";
    }

}
